/**
 * Copyright (c) 2002-2011 旭鸣信息
 * All rights reserved. 
 * File：GoodsDaoImpl.java
 * History:
 *       May 11, 2012: Initially created, liuzhi.
 */
package com.xteam.ecsystem.dao.Impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.xteam.ecsystem.base.SystemContent;
import com.xteam.ecsystem.dao.IGoodsDao;
import com.xteam.ecsystem.entities.BaseArticleInfo;
import com.xteam.ecsystem.entities.BaseGoodsAccessories;
import com.xteam.ecsystem.entities.BaseGoodsAttribute;
import com.xteam.ecsystem.entities.BaseGoodsBrand;
import com.xteam.ecsystem.entities.BaseGoodsGroup;
import com.xteam.ecsystem.entities.BaseGoodsInfo;
import com.xteam.ecsystem.entities.BaseGoodsPicture;
import com.xteam.ecsystem.entities.BaseGoodsType;
import com.xteam.ecsystem.entities.BaseGroupAttribute;
import com.xteam.ecsystem.entities.BaseRefArticle;
import com.xteam.ecsystem.entities.BaseRefGoods;
import com.xteam.ecsystem.entities.BaseSupplierInfo;
import com.xteam.ecsystem.entities.BaseTypeAttribute;
import com.xteam.ecsystem.entities.SysServerConfig;
import com.xteam.ecsystem.utils.AppConfig;
import com.xteam.ecsystem.utils.GoodsQueryCenterStation;
import com.xteam.ecsystem.utils.PageCenterStation;
import com.xteam.ecsystem.utils.StringUtil;
import com.xteam.ecsystem.utils.jmagick.ImageUtil;
import com.xteam.utils.UUID;

/**
 * 商品数据访问实现类
 * 
 * @author liuzhi
 * 
 */
@Repository
@SuppressWarnings("unchecked")
public class GoodsDaoImpl extends DDLPrefix implements IGoodsDao {
	
	@Autowired
	private BaseDaoImpl baseDao;

	public String saveOrUpdateObject(Object obj, String id) {
		try {
			BaseGoodsInfo goodsInfo = (BaseGoodsInfo) obj;

			// 随机产生货号
			String chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
			StringBuilder sbr = new StringBuilder();
			for (int i = 0; i < 10; i++) {
				if (i <= 2) {
					sbr.append(chars.charAt((int) (Math.random() * 26)));
				} else {
					sbr.append((int) (Math.random() * 10));
				}
			}

			if (goodsInfo.getBgiGsn() == null
					|| "".equals(goodsInfo.getBgiGsn())) {
				goodsInfo.setBgiGsn(sbr.toString());
			}

			// 新增或编辑时自动更新排序日期
			goodsInfo.setBgiOtime(new Date());

			if (id != null && !id.equals("") && id.length() > 0) {
				this.baseDao.update(goodsInfo);
				return id;
			} else {
				this.baseDao.save(goodsInfo);
				return goodsInfo.getBgiId();
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}

		return "";
	}

	public String getGoodsList(int pageIndex, int pageSize,
			GoodsQueryCenterStation goodsStation) {
		JSONObject jo = new JSONObject();
		List list = new ArrayList();
		String innerHql1 = "";
		String innerHql2 = "";
		String innerHql3 = "";
		String innerHql4 = "";
		String innerHql5 = "";
		
		try {
			String innerHql = " 1=1 ";
			String hql = SQL_FROM + BaseGoodsInfo.REF + SQL_WHERE + innerHql
					+ SQL_AND + " bgiState=1 ";

			String countHQL = SQL_SELECT + " count(*) " + SQL_FROM
					+ BaseGoodsInfo.REF + SQL_WHERE + innerHql + SQL_AND
					+ " bgiState=1 ";

			if (goodsStation != null) {
				if (goodsStation.getQueryBggTree() != null
						&& !"-1".equals(goodsStation.getQueryBggTree())) {
					hql += SQL_AND + " bgiGroup = ? ";
					countHQL += SQL_AND + " bgiGroup = ? ";
					list.add(goodsStation.getQueryBggTree());
				}

				if (goodsStation.getQueryBgiBrand() != null
						&& !"-1".equals(goodsStation.getQueryBgiBrand())) {
					hql += SQL_AND + " bgiBrand = ? ";
					countHQL += SQL_AND + " bgiBrand = ? ";
					list.add(goodsStation.getQueryBgiBrand());
				}

				if (goodsStation.getQueryBgiSupplier() != null
						&& !"-1".equals(goodsStation.getQueryBgiSupplier())) {
					hql += SQL_AND + " bgiSupplier = ? ";
					countHQL += SQL_AND + " bgiSupplier = ? ";
					list.add(goodsStation.getQueryBgiSupplier());
				}

				if (goodsStation.getQueryGoodsCommend() != null) {
					int goodsCommend = Integer.parseInt(goodsStation
							.getQueryGoodsCommend());
					
					switch (goodsCommend) {
					case 0:
						break;
					case 1:
						hql += SQL_AND + " bgiBest = 1 ";
						countHQL += SQL_AND + " bgiBest = 1 ";
						break;
					case 2:
						hql += SQL_AND + " bgiNew = 1 ";
						countHQL += SQL_AND + " bgiNew = 1 ";
						break;
					case 3:
						hql += SQL_AND + " bgiHot = 1 ";
						countHQL += SQL_AND + " bgiHot = 1 ";
						break;
					case 4:
						hql += SQL_AND + " bgiBuy = 1 ";
						countHQL += SQL_AND + " bgiBuy = 1 ";
						break;
					case 5:
						hql += SQL_AND + " bgiBest = 1 " + SQL_AND
								+ " bgiNew = 1 " + SQL_AND + " bgiHot = 1 "
								+ SQL_AND + " bgiBuy = 1 ";
						countHQL += SQL_AND + " bgiBest = 1 " + SQL_AND
								+ " bgiNew = 1 " + SQL_AND + " bgiHot = 1 "
								+ SQL_AND + " bgiBuy = 1 ";
						break;

					default:
						break;
					}
				}

				if (goodsStation.getQueryGoodsKeyword() != null
						&& !"".equals(goodsStation.getQueryGoodsKeyword())) {
					if (true) {
						innerHql1 = " (bgiName like ? ";
						hql = StringUtil.replace(hql, innerHql, innerHql1);
						countHQL = StringUtil.replace(countHQL, innerHql,
								innerHql1);
						list.add("%" + goodsStation.getQueryGoodsKeyword()
								+ "%");
					}

					if (true) {
						try {
							double bgiPrice = Double.parseDouble(goodsStation
									.getQueryGoodsKeyword());

							innerHql2 = innerHql1 + SQL_OR + " bgiPrice = ? ";
							hql = StringUtil.replace(hql, innerHql1, innerHql2);
							countHQL = StringUtil.replace(countHQL, innerHql1,
									innerHql2);

							list.add(bgiPrice);
						} catch (Exception e) {
						}
					}

					if (true) {
						try {
							int bgiWarn = Integer.parseInt(goodsStation
									.getQueryGoodsKeyword());

							innerHql3 = innerHql2 + SQL_OR + " bgiWarn = ? ";
							hql = StringUtil.replace(hql, innerHql2, innerHql3);
							countHQL = StringUtil.replace(countHQL, innerHql2,
									innerHql3);

							list.add(bgiWarn);
						} catch (Exception e) {
						}
					}

					if (true) {
						try {
							int bgiNumber = Integer.parseInt(goodsStation
									.getQueryGoodsKeyword());

							innerHql4 = innerHql3 + SQL_OR + " bgiNumber = ? ";
							hql = StringUtil.replace(hql, innerHql3, innerHql4);
							countHQL = StringUtil.replace(countHQL, innerHql3,
									innerHql4);

							list.add(bgiNumber);
						} catch (Exception e) {
						}
					}

					if (!"".equals(innerHql2) && "".equals(innerHql3)
							&& "".equals(innerHql4)) {
						innerHql5 = innerHql2 + SQL_OR + " bgiGsn like ? )";
						hql = StringUtil.replace(hql, innerHql2, innerHql5);
						countHQL = StringUtil.replace(countHQL, innerHql2,
								innerHql5);
						list.add("%" + goodsStation.getQueryGoodsKeyword()
								+ "%");
					} else if (!"".equals(innerHql2) && !"".equals(innerHql3)
							&& !"".equals(innerHql4)) {
						innerHql5 = innerHql4 + SQL_OR + " bgiGsn like ? )";
						hql = StringUtil.replace(hql, innerHql4, innerHql5);
						countHQL = StringUtil.replace(countHQL, innerHql4,
								innerHql5);
						list.add("%" + goodsStation.getQueryGoodsKeyword()
								+ "%");
					} else {
						innerHql5 = innerHql1 + SQL_OR + " bgiGsn like ? )";
						hql = StringUtil.replace(hql, innerHql1, innerHql5);
						countHQL = StringUtil.replace(countHQL, innerHql1,
								innerHql5);
						list.add("%" + goodsStation.getQueryGoodsKeyword()
								+ "%");
					}
				}

				if (goodsStation.getGoodsSortingCol() != null
						&& !"0".equals(goodsStation.getGoodsSortingCol())
						&& goodsStation.getGoodsSortingDir() != null
						&& !"0".equals(goodsStation.getGoodsSortingDir())) {
					hql += SQL_ORDERBY + goodsStation.getGoodsSortingCol()
							+ " " + goodsStation.getGoodsSortingDir();
					countHQL += SQL_ORDERBY + goodsStation.getGoodsSortingCol()
							+ " " + goodsStation.getGoodsSortingDir();
				}
			}

			PageCenterStation<?> p = (PageCenterStation<?>) this.baseDao
					.queryList(countHQL, hql, list.toArray(), pageIndex * pageSize, pageSize);

			jo = JSONObject.fromObject(p);

		} catch (Exception e) {
			e.printStackTrace();
		}

		return jo.toString();
	}

	public String getGoodsInfoById(String id) {
		JSONObject jo = new JSONObject();
		List list = new ArrayList();
		
		try {
			String hql = SQL_FROM + BaseGoodsInfo.REF + SQL_WHERE + " bgiId=?";
			list = this.baseDao.find(hql, new Object[] {id});

			jo = JSONObject.fromObject((BaseGoodsInfo) list.get(0));

		} catch (Exception e) {
			e.printStackTrace();
		}

		return jo.toString();
	}

	public void updateGoodsStateById(String id) {
		Class clazz = null;
		
		try {
			clazz = Class.forName("com.xteam.ecsystem.entities.BaseGoodsInfo");

			BaseGoodsInfo goodsInfo = (BaseGoodsInfo) this.baseDao.get(clazz,
					id);
			if (goodsInfo.getBgiState() == 1) {
				goodsInfo.setBgiState(0);
			} else {
				goodsInfo.setBgiState(1);
			}

			this.baseDao.saveOrUpdate(goodsInfo);

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public void updateBgiBestById(String id) {
		Class clazz = null;
		
		try {
			clazz = Class.forName("com.xteam.ecsystem.entities.BaseGoodsInfo");

			BaseGoodsInfo goodsInfo = (BaseGoodsInfo) this.baseDao.get(clazz,
					id);
			if (goodsInfo.getBgiBest() == 1) {
				goodsInfo.setBgiBest(0);
			} else {
				goodsInfo.setBgiBest(1);
			}

			this.baseDao.saveOrUpdate(goodsInfo);

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public void updateBgiBuyById(String id) {
		Class clazz = null;
		
		try {
			clazz = Class.forName("com.xteam.ecsystem.entities.BaseGoodsInfo");

			BaseGoodsInfo goodsInfo = (BaseGoodsInfo) this.baseDao.get(clazz,
					id);
			if (goodsInfo.getBgiBuy() == 1) {
				goodsInfo.setBgiBuy(0);
			} else {
				goodsInfo.setBgiBuy(1);
			}

			this.baseDao.saveOrUpdate(goodsInfo);

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public void updateBgiHotById(String id) {
		Class clazz = null;
		
		try {
			clazz = Class.forName("com.xteam.ecsystem.entities.BaseGoodsInfo");

			BaseGoodsInfo goodsInfo = (BaseGoodsInfo) this.baseDao.get(clazz,
					id);
			if (goodsInfo.getBgiHot() == 1) {
				goodsInfo.setBgiHot(0);
			} else {
				goodsInfo.setBgiHot(1);
			}

			this.baseDao.saveOrUpdate(goodsInfo);

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public void updateBgiNewById(String id) {
		Class clazz = null;
		
		try {
			clazz = Class.forName("com.xteam.ecsystem.entities.BaseGoodsInfo");

			BaseGoodsInfo goodsInfo = (BaseGoodsInfo) this.baseDao.get(clazz,
					id);
			if (goodsInfo.getBgiNew() == 1) {
				goodsInfo.setBgiNew(0);
			} else {
				goodsInfo.setBgiNew(1);
			}

			this.baseDao.saveOrUpdate(goodsInfo);

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public String getBggTreeSelect(String type) {
		JSONArray jarray = new JSONArray();
		List list = new ArrayList();
		
		try {

			String hql = SQL_FROM + BaseGoodsGroup.REF + SQL_WHERE + " 1=1 ";
			list = this.baseDao.find(hql);
			jarray = JSONArray.fromObject(list);

			if (type != null && !"".equals(type) && !"null".equals(type)) {
				if ("add".equals(type)) {
					JSONObject top = new JSONObject();
					top.put("bggId", "-1");
					top.put("bggName", "请选择...");
					top.put("bggParent", "0");
					jarray.add(0, top);
				} else if ("query".equals(type)) {
					JSONObject top = new JSONObject();
					top.put("bggId", "-1");
					top.put("bggName", "所有分类");
					top.put("bggParent", "0");
					jarray.add(0, top);
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return jarray.toString();
	}

	public String getBgbCombobox() {
		JSONArray jarray = new JSONArray();
		List list = new ArrayList();
		
		try {
			String hql = SQL_FROM + BaseGoodsBrand.REF + SQL_WHERE + " 1=1 ";
			list = this.baseDao.find(hql);

			jarray = JSONArray.fromObject(list);

		} catch (Exception e) {
			e.printStackTrace();
		}

		return jarray.toString();
	}

	public String getBgsCombobox() {
		JSONArray jarray = new JSONArray();
		List list = new ArrayList();
		
		try {
			String hql = SQL_FROM + BaseSupplierInfo.REF + SQL_WHERE + " 1=1 ";
			list = this.baseDao.find(hql);

			jarray = JSONArray.fromObject(list);

		} catch (Exception e) {
			e.printStackTrace();
		}

		return jarray.toString();
	}

	public String getAllGoodsTypeAttr(String btaGtype) {
		JSONArray array = new JSONArray();
		List list = new ArrayList();
		
		try {
			String hql = SQL_FROM + BaseTypeAttribute.REF + SQL_WHERE
					+ " btaGtype=? " + SQL_AND + " btaState=1 " + SQL_ORDERBY
					+ " btaOrder asc";
			list = this.baseDao.find(hql, new Object[] {btaGtype});

			array = JSONArray.fromObject(list);

		} catch (Exception e) {
			e.printStackTrace();
		}

		return array.toString();
	}

	public void saveGoodsAttr(String bgiId, String btaGtype,
			JSONArray dynamicArray, JSONArray staticArray) {
		try {
			delGoodsAttrByBgiId(bgiId);

			if (dynamicArray != null && dynamicArray.size() > 0) {
				for (int i = 0; i < dynamicArray.size(); i++) {
					JSONObject dyObj = dynamicArray.getJSONObject(i);
					BaseGoodsAttribute bga = (BaseGoodsAttribute) JSONObject
							.toBean(dyObj, BaseGoodsAttribute.class);
					if("".equals(bga.getBgaId())||bga.getBgaId()==null){
						bga.setBgaId(UUID.getUUID());
					}
					bga.setBgaGoods(bgiId);
					bga.setBgaGtype(btaGtype);
					
					this.baseDao.save(bga);
				}
			}

			if (staticArray != null && staticArray.size() > 0) {
				for (int j = 0; j < staticArray.size(); j++) {
					JSONObject dyObj = staticArray.getJSONObject(j);
					BaseGoodsAttribute bga = (BaseGoodsAttribute) JSONObject
							.toBean(dyObj, BaseGoodsAttribute.class);
					bga.setBgaId(UUID.getUUID());
					bga.setBgaGoods(bgiId);
					bga.setBgaGtype(btaGtype);

					this.baseDao.save(bga);
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void delGoodsAttrByBgiId(String bgaGoods) {
		String hql = "";
		
		try {
			hql = SQL_DELETE + SQL_FROM + BaseGoodsAttribute.REF + SQL_WHERE
					+ " bgaGoods=? ";
			this.baseDao.executeUpdate(hql, new Object[] { bgaGoods });
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public String getGoodsAttrByBgiId(String id) {
		JSONArray jo = new JSONArray();
		List list = new ArrayList();
		
		try {
			String hql = SQL_SELECT + "b.btaAtype, b.btaMode,b.btaName,b.btaValue, a.bgaId, a.bgaGoods, " 
					+ "a.bgaGtype, a.bgaTattri, a.bgaValue, a.bgaPrice, a.bgaOrder "
					+ SQL_FROM
					+ BaseGoodsAttribute.REF + " a, "
					+ BaseTypeAttribute.REF + " b "
					+ SQL_WHERE
					+ " 1=1 "
					+ SQL_AND
					+ " a.bgaGoods=? and a.bgaTattri=b.btaId  order by a.bgaOrder,a.bgaTattri";
			
			list = this.baseDao.find(hql, new Object[]{id});

			if (list != null && !list.isEmpty()) {
				jo = JSONArray.fromObject(list);
				return jo.toString();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return "";
	}
	
	public String getGoodsAttrTab(String bgaGoods){
		JSONArray jsonArray = new JSONArray();
		List list = new ArrayList();
		
		try {
			String hql = SQL_FROM + BaseGoodsAttribute.REF + SQL_WHERE + " bgaGoods = ? ";
			list = this.baseDao.find(hql, new Object[] {bgaGoods});

			if (list != null && !list.isEmpty()) {
				BaseGoodsAttribute bga = (BaseGoodsAttribute)list.get(0);
				jsonArray = getGoodsTypeAttrs(bgaGoods, bga.getBgaGtype());
			}

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		
		return jsonArray.toString();
	}
	
	private JSONArray getGoodsTypeAttrs(String bgaGoods, String btaGtype){
		JSONArray jsonArray = new JSONArray();
		List list = new ArrayList();
		
		try {
			String sql = SQL_SELECT 
					+ "bta.bta_atype, bta.bta_mode, bta.bta_name, bta.bta_value, bga.bga_id, " 
					+ "bga.bga_goods, bga.bga_gtype, bga.bga_tattri, bga.bga_value, bga.bga_price, " 
					+ "bga.bga_order, bta.bta_id "
					+ SQL_FROM
					+ "base_type_attribute as bta "
					+ "left join "
					+ "base_goods_attribute as bga "
					+ SQL_ON
					+ "bga.bga_tattri = bta.bta_id "
					+ SQL_AND
					+ "bga.bga_goods = '" + bgaGoods + "'"
					+ SQL_WHERE 
					+ "bta.bta_gtype = '" + btaGtype + "' order by bta.bta_order";
			
			list = this.baseDao.queryListWithSql(sql);

			if (list != null && !list.isEmpty()) {
				jsonArray = JSONArray.fromObject(list);
			}

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

		return jsonArray;
	}
	
	public String getBgtByBggId(String bgaGgroup){
		JSONArray jsonArray = new JSONArray();
		List list = new ArrayList();
		List list2 = new ArrayList();
		
		try {

			String hql = SQL_FROM + BaseGroupAttribute.REF + SQL_WHERE + " bgaGgroup = ? ";
			list = this.baseDao.find(hql, new Object[] {bgaGgroup});

			if (list != null && !list.isEmpty()) {
				BaseGroupAttribute bga = (BaseGroupAttribute)list.get(0);
				hql = SQL_FROM + BaseGoodsType.REF + SQL_WHERE + "bgtId = ? ";
				list2 = this.baseDao.find(hql, new Object[]{bga.getBgaGtype()});
				
				if (list2 != null && !list2.isEmpty()) {
					jsonArray = JSONArray.fromObject(list2);
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		
		return jsonArray.toString();
	}
	
	/**
	 * 加载商品属性表格数据，进而绘制该表格
	 * @see com.xteam.ecsystem.dao.IGoodsDao#getBgaTabData(java.lang.String)
	 */
	public String getBgaTabData(String bggId){
		JSONArray jsonArray = new JSONArray();
		List list = new ArrayList();
		
		try {

			// 1、根据商品分类查询商品类型属性字段列表
			String hql = SQL_SELECT + "bgaTattri" + SQL_FROM + BaseGroupAttribute.REF 
					+ SQL_WHERE + " bgaGgroup = ? " + SQL_ORDERBY + "bgaOrder" +SQL_DESC;
			
			list = this.baseDao.find(hql, new Object[] {bggId});
			
			if (list != null && !list.isEmpty()) {
				StringBuilder bgaTattriStr = new StringBuilder();
				
				for (int i = 0; i < list.size() - 1; i++) {
					bgaTattriStr.append("'" + list.get(i) + "',");
				}
				bgaTattriStr.append("'" + list.get(list.size() - 1) + "'");
				
				// 2、根据商品类型属性ID查询商品类型属性列表
				hql = SQL_FROM + BaseTypeAttribute.REF + SQL_WHERE + "btaId in(" 
						+ bgaTattriStr.toString() + ")" + SQL_AND + "btaState = 1" 
						+ SQL_ORDERBY + "btaOrder asc";
				
				list = this.baseDao.find(hql);		
				jsonArray = JSONArray.fromObject(list);
				
			}

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		
		return jsonArray.toString();
	}
	
	/**
	 * 绘制指定商品的属性列表
	 * @see com.xteam.ecsystem.dao.IGoodsDao#getBgaTabPrint(java.lang.String)
	 */
	public String getBgaTabPrint(String bggId, String bgiGroup){
		JSONArray jsonArray = new JSONArray();
		List list = new ArrayList();
		
		try {
			String sql = "select " 
					+ "bta.bta_atype, bta.bta_mode, bta.bta_name, bta.bta_value, bga.bga_id, " 
					+ "bga.bga_goods, bga.bga_gtype, bga.bga_tattri, bga.bga_value, bga.bga_price, " 
					+ "bga.bga_order, bta.bta_id "
					+ "from "
					+ "base_group_attribute as main " 
					+ "left join "
					+ "base_goods_attribute as bga "
					+ "on main.bga_tattri = bga.bga_tattri "
					+ "and bga.bga_goods = '" + bggId + "' "
					+ "inner join " 
					+ "base_type_attribute as bta "
					+ "on main.bga_tattri = bta.bta_id "
					+ "and bta.bta_state = 1 "
					+ "where " 
					+ "main.bga_ggroup = '" + bgiGroup + "' " 
					+ "order by main.bga_order desc";
			
			list = this.baseDao.queryListWithSql(sql);

			if (list != null && !list.isEmpty()) {
				jsonArray = JSONArray.fromObject(list);
			}

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

		return jsonArray.toString();
	}
	
	public String getGoodsPicture(int pageIndex, int pageSize, String id) {
		JSONObject jo = new JSONObject();
		List list = new ArrayList();
		
		try {
			String hql = SQL_FROM + "BaseGoodsPicture" + SQL_WHERE + " 1=1 ";
			String countHQL = SQL_SELECT + " count(*) " + SQL_FROM
					+ "BaseGoodsPicture" + SQL_WHERE + " 1=1 ";
			if (id != null && id.length() > 0 && !id.equals("undefined")
					&& !id.equals("null")) {
				hql += SQL_AND + " bgpGoods=? order by bgpOrder asc";
				countHQL += SQL_AND + " bgpGoods=?";
				list.add(id);
			}
			PageCenterStation<?> p = (PageCenterStation<?>) this.baseDao
					.queryList(countHQL, hql, list.toArray(), pageIndex * pageSize, pageSize);
			jo = JSONObject.fromObject(p);
			return jo.toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}

	public boolean saveZoomImg(BaseGoodsPicture xt, SysServerConfig serverCfg) {
		if (xt.getBgpBig() == null || "".equals(xt.getBgpBig())) {
			ImageUtil ImageUtil = new ImageUtil();
			ImageUtil.listFormat();
			
			try {
				String prefix = xt.getBgpOriginal().substring(0,
						xt.getBgpOriginal().lastIndexOf("."));
				String suffix = xt.getBgpOriginal().substring(
						xt.getBgpOriginal().lastIndexOf("."),
						xt.getBgpOriginal().length());
				String imgsPath = AppConfig.getRealPath()+AppConfig.getProductImagePath();
				
				ImageUtil.zoomByWidthAndHeight(imgsPath + xt.getBgpOriginal(), 
						imgsPath + prefix + "_small" + suffix, 
						serverCfg.getSscGsheight(), serverCfg.getSscGswidth(), false);
				ImageUtil.zoomByWidthAndHeight(imgsPath + xt.getBgpOriginal(), 
						imgsPath + prefix + "_middle" + suffix, 
						serverCfg.getSscGmheight(), serverCfg.getSscGmwidth(), false);
				ImageUtil.zoomByWidthAndHeight(imgsPath + xt.getBgpOriginal(), 
						imgsPath + prefix + "_big" + suffix, 
						serverCfg.getSscGbheight(), serverCfg.getSscGbwidth(), false);
				
				xt.setBgpSmall(prefix + "_small" + suffix);
				xt.setBgpMiddle(prefix + "_middle" + suffix);
				xt.setBgpBig(prefix + "_big" + suffix);
			} catch (Exception e) {
				e.printStackTrace();
				return false;
			}
		}
		return true;
	}

	public void saveOrUpdatePicture(Object obj, String id, SysServerConfig serverCfg) {
		BaseGoodsPicture xt = (BaseGoodsPicture) obj;
		if (id != null && !"".equals(id)) {
			if (saveZoomImg(xt, serverCfg))
				baseDao.update(obj);
		} else {
			if (saveZoomImg(xt, serverCfg))
				baseDao.save(obj);
		}

	}

	public void delPicture(String id) {
		try {
			String sql = "delete  from  base_goods_picture where  bgp_id='"
					+ id + "'";
			baseDao.executeUpdateWithSQL(sql, (Object[]) null);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public String getGoodsTrashList(int pageIndex, int pageSize,
			String bgiKeyword) {
		JSONObject jo = new JSONObject();
		List list = new ArrayList();
		String innerHql1 = "";
		String innerHql2 = "";
		String innerHql3 = "";
		
		try {
			String innerHql = " 1=1 ";
			String hql = SQL_FROM + BaseGoodsInfo.REF + SQL_WHERE + innerHql
					+ SQL_AND + " bgiState=0 ";

			String countHQL = SQL_SELECT + " count(*) " + SQL_FROM
					+ BaseGoodsInfo.REF + SQL_WHERE + innerHql + SQL_AND
					+ " bgiState=0 ";

			if (bgiKeyword != null && !"".equals(bgiKeyword)
					&& !"null".equals(bgiKeyword)
					&& !"undefined".equals(bgiKeyword)) {
				if (true) {
					innerHql1 = " (bgiName like ? ";
					hql = StringUtil.replace(hql, innerHql, innerHql1);
					countHQL = StringUtil
							.replace(countHQL, innerHql, innerHql1);
					list.add("%" + bgiKeyword + "%");
				}

				if (true) {
					try {
						double bgiPrice = Double.parseDouble(bgiKeyword);

						innerHql2 = innerHql1 + SQL_OR + " bgiPrice = ? ";
						hql = StringUtil.replace(hql, innerHql1, innerHql2);
						countHQL = StringUtil.replace(countHQL, innerHql1,
								innerHql2);

						list.add(bgiPrice);
					} catch (Exception e) {
					}
				}

				if (!"".equals(innerHql2)) {
					innerHql3 = innerHql2 + SQL_OR + " bgiGsn like ? )";
					hql = StringUtil.replace(hql, innerHql2, innerHql3);
					countHQL = StringUtil.replace(countHQL, innerHql2,
							innerHql3);
					list.add("%" + bgiKeyword + "%");
				} else {
					innerHql3 = innerHql1 + SQL_OR + " bgiGsn like ? )";
					hql = StringUtil.replace(hql, innerHql1, innerHql3);
					countHQL = StringUtil.replace(countHQL, innerHql1,
							innerHql3);
					list.add("%" + bgiKeyword + "%");
				}
			}

			PageCenterStation<?> p = (PageCenterStation<?>) this.baseDao
					.queryList(countHQL, hql, list.toArray(), pageIndex * pageSize, pageSize);

			jo = JSONObject.fromObject(p);

		} catch (Exception e) {
			e.printStackTrace();
		}

		return jo.toString();
	}

	public void delGoods(String ids) {
		Class clazz = null;
		String hql = "";
		
		try {
			clazz = Class.forName("com.xteam.ecsystem.entities.BaseGoodsInfo");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		List list = new ArrayList();
		String oId[] = ids.split(",");
		for (String id : oId) {
			Object obj = this.baseDao.get(clazz, id);
			
			if (obj != null) {
				list.add(obj);
			}
			
			// 1、先删除该商品对应的属性列表
			hql = SQL_DELETE + SQL_FROM + BaseGoodsAttribute.REF + SQL_WHERE + "bgaGoods = ?";
			this.baseDao.executeUpdate(hql, new Object[]{id});
		}
		
		// 2、再删除本商品记录
		this.baseDao.deleteAll(list);
	}

	public void saveRevertGoods(String ids) {
		Class clazz = null;
		
		try {
			clazz = Class.forName("com.xteam.ecsystem.entities.BaseGoodsInfo");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		String oId[] = ids.split(",");
		for (String id : oId) {
			BaseGoodsInfo bgi = (BaseGoodsInfo) this.baseDao.get(clazz, id);
			if (bgi != null) {
				bgi.setBgiState(1);
				this.baseDao.saveOrUpdate(bgi);
			}
		}
	}

	/**
	 * @see com.xteam.ecsystem.dao.IGoodsDao#getSystemContent(int,
	 *      java.lang.String, java.lang.String, java.lang.String)
	 */
	public List<Map<String, String>> getSystemContent(int state, String value,
			String text, String parent) {
		String hql = "select bgiId as " + value + ", bgiName as " + text
				+ ", '' as " + parent + " from BaseGoodsInfo where bgiState = "
				+ state;
		return this.baseDao.queryListInMap(hql);
	}

	/**
	 * @see com.xteam.ecsystem.dao.IGoodsDao#getSystemContents(java.lang.String,
	 *      int, int, int)
	 */
	public PageCenterStation<SystemContent> getSystemContents(String name,
			int state, int first, int max) {
		String suffix = " from base_goods_info c left join base_goods_group p on c.bgi_group = p.bgg_id "
				+ "where c.bgi_state = " + state;
		if (name != null && !"".equals(name)) {
			suffix += " and c.bgi_name like '%" + name + "%'";
		}

		String countSql = "select count(c.bgi_id)" + suffix;
		long count = this.baseDao.queryCountWithSql(countSql);

		List<SystemContent> list = new ArrayList<SystemContent>();
		if (count > 0) {
			String querySql = "select c.bgi_id as value, c.bgi_name as text, p.bgg_name as parent"
					+ suffix;
			List<Object[]> data = this.baseDao.queryListWithSql(querySql);
			if (data != null) {
				for (Object[] o : data) {
					String value = (o[0] == null) ? null : o[0].toString();
					String text = (o[1] == null) ? null : o[1].toString();
					String parent = (o[2] == null) ? null : o[2].toString();
					list.add(new SystemContent(value, text, parent));
				}
			}
		}

		return new PageCenterStation<SystemContent>(count, list);
	}

	public String findPicture(String bgpId) {
		JSONObject obj = new JSONObject();
		String sql = "from BaseGoodsPicture where bgpId='" + bgpId + "'";
		List list = baseDao.find(sql);
		
		if (list != null && list.size() > 0) {
			BaseGoodsPicture gpicture = (BaseGoodsPicture) list.get(0);
			obj = JSONObject.fromObject(gpicture);
		}
		
		return obj.toString();
	}

	public void saveAccessories(String ids, String bgiId) {
		// 删除原有配件
		String hql = "";
		
		try {
			hql = SQL_DELETE + SQL_FROM + BaseGoodsAccessories.REF + SQL_WHERE
					+ " bgaGid = ? ";
			this.baseDao.executeUpdate(hql, new Object[] { bgiId });
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		// 重新设置配件
		String oId[] = ids.split(",");
		for (String id : oId) {
			BaseGoodsAccessories bga = new BaseGoodsAccessories();
			bga.setBgaGid(bgiId);
			bga.setBgaAid(id);
			this.baseDao.saveOrUpdate(bga);
		}
	}

	/**
	 * 查询所有商品配件列表
	 * @param bgiId
	 * where 配件表：配件ID=商品表：商品ID
	 */
	public String getAccessoriesList(int pageIndex, int pageSize, String bgaGid) {
		JSONObject jo = new JSONObject();
		List list = new ArrayList();
		
		if (bgaGid != null) {
			String hql = SQL_SELECT + " new " + BaseGoodsInfo.REF
					+ " (bgi.bgiId, bgi.bgiName)" + SQL_FROM
					+ BaseGoodsAccessories.REF + " as bga, "
					+ BaseGoodsInfo.REF + " as bgi " + SQL_WHERE
					+ " bga.bgaAid = bgi.bgiId " + SQL_AND + " bga.bgaGid = ? ";
			String countHQL = SQL_SELECT + " count(*) " + SQL_FROM
					+ BaseGoodsAccessories.REF + " as bga, "
					+ BaseGoodsInfo.REF + " as bgi " + SQL_WHERE
					+ " bga.bgaAid = bgi.bgiId " + SQL_AND + " bga.bgaGid = ? ";
			list.add(bgaGid);
			
			PageCenterStation<?> p = (PageCenterStation<?>) this.baseDao
					.queryList(countHQL, hql, list.toArray(), pageIndex * pageSize, pageSize);

			jo = JSONObject.fromObject(p);
			return jo.toString();
		} else {
			return null;
		}
	}

	/**
	 * 查询所有相关商品列表
	 * @param bgiId
	 * where 商品关联表：关联商品ID=商品表：商品ID
	 */
	public String getAssociationList(int pageIndex, int pageSize,
			String brgMgoods) {
		JSONObject jo = new JSONObject();
		List list = new ArrayList();
		
		if (brgMgoods != null) {
			String hql = SQL_SELECT + " new " + BaseGoodsInfo.REF
					+ " (bgi.bgiId, bgi.bgiName, brf.brgIsDouble, bgi.bgiPrice)" + SQL_FROM
					+ BaseRefGoods.REF + " as brf, " + BaseGoodsInfo.REF
					+ " as bgi " + SQL_WHERE + " brf.brgRgoods = bgi.bgiId "
					+ SQL_AND + " brf.brgMgoods = ? ";
			String countHQL = SQL_SELECT + " count(*) " + SQL_FROM
					+ BaseRefGoods.REF + " as brf, " + BaseGoodsInfo.REF
					+ " as bgi " + SQL_WHERE + " brf.brgRgoods = bgi.bgiId "
					+ SQL_AND + " brf.brgMgoods = ? ";
			list.add(brgMgoods);
			
			PageCenterStation<?> p = (PageCenterStation<?>) this.baseDao
					.queryList(countHQL, hql, list.toArray(), pageIndex * pageSize, pageSize);

			jo = JSONObject.fromObject(p);
			return jo.toString();
		} else {
			return null;
		}
	}

	public void saveGoodsAricles(String baiIds, String bgiId) {
		// 删除原有文章关联
		String hql = "";
		
		try {
			hql = SQL_DELETE + SQL_FROM + BaseRefArticle.REF + SQL_WHERE
					+ " braGid = ? ";
			this.baseDao.executeUpdate(hql, new Object[] { bgiId });
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		// 重新设置文章关联
		String oId[] = baiIds.split(",");
		for (String id : oId) {
			BaseRefArticle bai = new BaseRefArticle();
			bai.setBraGid(bgiId);
			bai.setBraAid(id);
			this.baseDao.saveOrUpdate(bai);
		}
	}

	/**
	 * 查询所有相关文章列表
	 * @param bgiId
	 * where 文章关联表：关联文章ID=商品表：商品ID
	 */
	public String getArticlesList(int pageIndex, int pageSize, String braGid) {
		JSONObject jo = new JSONObject();
		List list = new ArrayList();
		
		if (braGid != null) {
			String hql = SQL_SELECT + " new " + BaseArticleInfo.REF
					+ " (bai.baiId, bai.baiTitle)" + SQL_FROM
					+ BaseArticleInfo.REF + " as bai, " + BaseRefArticle.REF
					+ " as bra " + SQL_WHERE + " bai.baiId = bra.braAid "
					+ SQL_AND + " bra.braGid = ? ";
			String countHQL = SQL_SELECT + " count(*) " + SQL_FROM
					+ BaseArticleInfo.REF + " as bai, " + BaseRefArticle.REF
					+ " as bra " + SQL_WHERE + " bai.baiId = bra.braAid "
					+ SQL_AND + " bra.braGid = ? ";
			list.add(braGid);
			PageCenterStation<?> p = (PageCenterStation<?>) this.baseDao
					.queryList(countHQL, hql, list.toArray(), pageIndex * pageSize, pageSize);

			jo = JSONObject.fromObject(p);
			return jo.toString();
		} else {
			return null;
		}
	}

	/**
	 * 关联商品时，区分单向关联和双向关联
	 * 1、单向关联（0）：商品A关联商品B，通过商品A可以直接查询到商品B。
	 * 2、双向关联（1）：商品A管联商品B，同时商品B又关联商品A。
	 * 因此在查询某商品的关联商品时，要站在主动关联和被动关联两个角度去写查询语句。即：
	 * 
	 * sql-1: select * from BaseRefGoods where brgMgoods='当前商品ID' and brgIsDouble='0'；
	 * sql-2: select * from BaseRefGoods where brgRgoods='当前商品ID' and brgIsDouble='1'；
	 * 
	 * @see com.xteam.ecsystem.dao.IGoodsDao#saveAssociation(java.lang.String, java.lang.String, java.lang.String)
	 */
	public void saveAssociation(String brgRgoods, String brgIsDoulbes,
			String bgiId) {
		// 删除原有商品关联
		String hql = "";
		
		try {
			hql = SQL_DELETE + SQL_FROM + BaseRefGoods.REF + SQL_WHERE
					+ " brgMgoods = ? ";
			this.baseDao.executeUpdate(hql, new Object[] {bgiId});
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		// 重新设置商品关联
		String brgRgood[] = brgRgoods.split(",");
		String brgIsDouble[] = brgIsDoulbes.split(",");
		
		for (int i = 0; i < brgRgood.length; i++) {
			BaseRefGoods brf = new BaseRefGoods();
			brf.setBrgMgoods(bgiId);
			brf.setBrgRgoods(brgRgood[i]);
			brf.setBrgIsDouble(brgIsDouble[i]);
			
			this.baseDao.saveOrUpdate(brf);
		}
	}
	
	/**
	 * 保存商品关联
	 * @see com.xteam.ecsystem.dao.IGoodsDao#saveBrgInfo(java.lang.String, java.lang.String, java.lang.String)
	 */
	public void saveBrgInfo(String brgRgoods, String brgIsDoulbes, String bgiId){
		String hql = "";
		
		try {
			// 1、删除原有商品关联
			hql = SQL_DELETE + SQL_FROM + BaseRefGoods.REF + SQL_WHERE + "brgMgoods = ?";
			this.baseDao.executeUpdate(hql, new Object[] {bgiId});
			
			// 2、并将原有双向关联产品统一改为单向关联
			hql = SQL_UPDATE + BaseRefGoods.REF + SQL_SET + "brgIsDouble = 0" 
				+ SQL_WHERE + "brgRgoods = ? and brgIsDouble = 1";
			this.baseDao.executeUpdate(hql, new Object[] {bgiId});
			
			// 3、重新设置商品关联
			if (brgRgoods != null && !"".equals(brgRgoods)) {
				String brgRgood[] = brgRgoods.split(",");
				String brgIsDouble[] = brgIsDoulbes.split(",");
				
				for (int i = 0; i < brgRgood.length; i++) {
					BaseRefGoods brf = new BaseRefGoods();
					brf.setBrgMgoods(bgiId);
					brf.setBrgRgoods(brgRgood[i]);
					brf.setBrgIsDouble(brgIsDouble[i]);
					// 4、保存单向关联商品（1,4对应）
					this.baseDao.save(brf);
					
					if (Integer.parseInt(brgIsDouble[i]) == 1) {
						// 5、删除关联商品和本商品的关联以便重新建立关联（2,5,6对应）
						hql = SQL_DELETE + SQL_FROM + BaseRefGoods.REF + SQL_WHERE 
							+ "brgMgoods = ? and brgRgoods = ?";
						this.baseDao.executeUpdate(hql, new Object[] {brgRgood[i], bgiId});
						
						brf = new BaseRefGoods();
						brf.setBrgMgoods(brgRgood[i]);
						brf.setBrgRgoods(bgiId);
						brf.setBrgIsDouble(brgIsDouble[i]);
						// 6、保存双向关联商品
						this.baseDao.save(brf);
					}
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 上传商品图片时，保存上传后的文件名,并生成大中小图。
	 * @param fileNames,bgiId
	 */
	public void uploadGoodsPic(String fileNames, String bgiId, SysServerConfig serverCfg) {
		if (fileNames != null) {
			String bgpOriginal[] = fileNames.split(",");
			for (int i = 0, len = bgpOriginal.length; i < len; i++) {
				BaseGoodsPicture bgp = new BaseGoodsPicture();
				bgp.setBgpGoods(bgiId);
				bgp.setBgpOriginal(bgpOriginal[i]);
				bgp.setBgpState(0);
				bgp.setBgpTime(new Date());
				
				//这里完成生成中小大图
				saveZoomImg(bgp, serverCfg);
				
				this.baseDao.saveOrUpdate(bgp);
			}
		}
	}

	/**
	 * 根据isDeleted判断图片是删除还是更新操作，批量删除或更新图片信息
	 * @param bgpId,bgpDesc,bgpState,bgpOrder,isDeleted
	 */
	public void updatePics(String[] bgpId, String[] bgpDesc, String[] bgpState,
			String[] bgpOrder, String[] isDeleted) {
		if (bgpId != null) {
			for (int i = 0, len = bgpId.length; i < len; i++) {
				if (Integer.parseInt(isDeleted[i]) == 0 || isDeleted[i] == "0") {
					try {
						String hql = "update BaseGoodsPicture set bgpDesc = ?,bgpState = ?,bgpOrder = ?,bgpTime = CURRENT_TIMESTAMP where bgpId = ?";
						
						int tempOrder = 0;
						try {
							tempOrder = Integer.parseInt(bgpOrder[i]);
						} catch (NumberFormatException ne) {
						}
						
						baseDao.executeUpdate(hql, new Object[] { bgpDesc[i], Integer.parseInt(bgpState[i]), tempOrder, bgpId[i] });
					} catch (Exception E) {
						E.printStackTrace();
					}
				} else if (Integer.parseInt(isDeleted[i]) == 1
						|| isDeleted[i] == "1") {
					String hql = "";
					try {
						hql = SQL_DELETE + SQL_FROM + BaseGoodsPicture.REF
								+ SQL_WHERE + " bgpId = ? ";
						this.baseDao.executeUpdate(hql,
								new Object[] { bgpId[i] });
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		}
	}

}
